import mysql.connector
from mysql.connector import Error

def update_database():
    connection = None
    try:
        # 连接到MySQL服务器和数据库
        connection = mysql.connector.connect(
            host='ip',
            port=port,
            user='root',
            password='password',
            database='erp_db'
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 创建供应商表
            create_supplier_table = """
            CREATE TABLE IF NOT EXISTS supplier (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                code VARCHAR(50) NOT NULL COMMENT '供应商编码',
                name VARCHAR(100) NOT NULL COMMENT '供应商名称',
                contact_person VARCHAR(50) COMMENT '联系人',
                phone VARCHAR(20) COMMENT '联系电话',
                email VARCHAR(100) COMMENT '邮箱',
                address VARCHAR(200) COMMENT '地址',
                bank_name VARCHAR(100) COMMENT '开户银行',
                bank_account VARCHAR(50) COMMENT '银行账户',
                tax_number VARCHAR(50) COMMENT '税号',
                status ENUM('ACTIVE','INACTIVE') DEFAULT 'ACTIVE' COMMENT '状态',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted TINYINT(1) DEFAULT 0,
                UNIQUE KEY uk_code (code)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商表'
            """
            cursor.execute(create_supplier_table)
            print("供应商表创建成功")
            
            # 创建采购订单表
            create_purchase_order_table = """
            CREATE TABLE IF NOT EXISTS purchase_order (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                order_no VARCHAR(50) NOT NULL COMMENT '采购订单号',
                supplier_id BIGINT NOT NULL COMMENT '供应商ID',
                order_date DATE NOT NULL COMMENT '订单日期',
                delivery_date DATE COMMENT '预计交货日期',
                total_amount DECIMAL(15,2) COMMENT '总金额',
                status ENUM('DRAFT','APPROVED','PARTIAL_RECEIVED','RECEIVED','CANCELLED') DEFAULT 'DRAFT' COMMENT '状态',
                created_by BIGINT NOT NULL COMMENT '创建人',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted TINYINT(1) DEFAULT 0,
                UNIQUE KEY uk_order_no (order_no),
                KEY idx_supplier_id (supplier_id),
                KEY idx_status (status),
                CONSTRAINT fk_purchase_order_supplier FOREIGN KEY (supplier_id) REFERENCES supplier (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购订单表'
            """
            cursor.execute(create_purchase_order_table)
            print("采购订单表创建成功")
            
            # 创建采购订单明细表
            create_purchase_order_item_table = """
            CREATE TABLE IF NOT EXISTS purchase_order_item (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                order_id BIGINT NOT NULL COMMENT '订单ID',
                product_id BIGINT NOT NULL COMMENT '产品ID',
                quantity DECIMAL(15,6) NOT NULL COMMENT '数量',
                unit_price DECIMAL(15,6) NOT NULL COMMENT '单价',
                amount DECIMAL(15,2) COMMENT '金额',
                received_quantity DECIMAL(15,6) DEFAULT 0 COMMENT '已收货数量',
                unit VARCHAR(20) COMMENT '单位',
                KEY idx_order_id (order_id),
                KEY idx_product_id (product_id),
                CONSTRAINT fk_purchase_order_item_order FOREIGN KEY (order_id) REFERENCES purchase_order (id) ON DELETE CASCADE,
                CONSTRAINT fk_purchase_order_item_product FOREIGN KEY (product_id) REFERENCES product (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购订单明细表'
            """
            cursor.execute(create_purchase_order_item_table)
            print("采购订单明细表创建成功")
            
    except Error as e:
        print(f"更新数据库时出错: {e}")
        
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL连接已关闭")

if __name__ == "__main__":
    update_database()